begin;

drop VIEW brc_view;

create or replace view brc_view as select brc_id,transaction_id,transaction_type,description,export_promotion,export_promotion_date,bill_of_lading,bill_of_lading_date,value1,value2,bill_amount,freight_amount,insurance_amount,commission_paid,convertable,fob_value,bank_ref_no,date_of_export,date_of_realization,form_no,category_id,sign_name,designation,CASE   transaction_type WHEN 'coc' THEN (SELECT invoice_no||'/'||invoice_date from certificate_of_origin_commercial where commercial_id=brc.transaction_id) WHEN 'gsp'  THEN (SELECT nos_invoice_date from gsp_item_details where gsp_id=brc.transaction_id and nos_invoice_date <> '') END as invoice_no from _100351.brc;
/*
CREATE VIEW invoice_details as (SELECT gsp_id as gsp_id, nos_invoice_date||' /gsp' as invoice_no from gsp_item_details where nos_invoice_date<>'') Union (Select commercial_id as gsp_id,invoice_no||' / '||invoice_date||' /coc' from certificate_of_origin_commercial where invoice_no<>'');
*/
CREATE VIEW invoice_details as (SELECT gsp_id||'-gsp' as gsp_id, nos_invoice_date||' / gsp' as invoice_no from gsp_item_details where nos_invoice_date<>'') Union (Select commercial_id||'-coc' as gsp_id,invoice_no||'/'||invoice_date||' / coc' from certificate_of_origin_commercial where invoice_no<>'');
commit;